from UI.ui_DN import Ui_MainWindow
import sys
import pyodbc
from PyQt5.QtWidgets import QApplication, QMainWindow, QMessageBox
from PyQt5.QtCore import pyqtSignal, QSettings, Qt, QTimer


class MainWindow(QMainWindow, Ui_MainWindow):
    show_dialog = pyqtSignal(str)

    def __init__(self):
        super().__init__()
        self.setupUi(self)

        self.show_dialog.connect(self.show_info_dialog)

        self.conn = None
        self.settings = None
        self.driver = None
        self.server = None
        self.database = None
        self.username = None
        self.password = None

        print(pyodbc.drivers())

        self.load_db_config()
        self.connect_to_db()

        self.pushButton_page_0.clicked.connect(self.change_0)
        self.pushButton_page_1.clicked.connect(self.change_1)
        self.pushButton_page_2.clicked.connect(self.change_2)
        self.pushButton_all_players.clicked.connect(self.all_players)


    """弹框功能"""
    def show_info_dialog(self, msg):
        self.showNormal()  # 从托盘恢复
        self.activateWindow()  # 激活窗口
        self.raise_()  # 置顶窗口
        QApplication.processEvents()  # 立即处理UI事件
        msg_box = QMessageBox(
            QMessageBox.Information,
            "提示",
            msg,
            QMessageBox.Ok,
            self  # 关键修改！将父窗口设为MainWindow
        )
        msg_box.setWindowFlags(Qt.Dialog | Qt.WindowStaysOnTopHint)
        msg_box.setWindowModality(Qt.ApplicationModal)
        msg_box.activateWindow()
        msg_box.raise_()
        # 6. 显示对话框
        msg_box.exec_()

    def change_page(self, index):
        self.stackedWidget.setCurrentIndex(index)

    def change_0(self):
        self.change_page(0)

    def change_1(self):
        self.change_page(1)

    def change_2(self):
        self.change_page(2)

    def load_db_config(self):
        """
        从 sqlserver.ini 读取数据库配置
        """
        self.settings = QSettings("sqlserver.ini", QSettings.IniFormat)
        self.driver = self.settings.value("Database/Driver")
        self.server = self.settings.value("Database/Server")
        self.database = self.settings.value("Database/Database")
        self.username = self.settings.value("Database/Username")
        self.password = self.settings.value("Database/Password")

        # 检查是否读取成功
        if not all([self.server, self.database, self.username, self.password]):
            self.show_info_dialog("配置文件不完整，请检查 sqlserver.ini")

    def connect_to_db(self):
        try:
            self.conn = pyodbc.connect(
                "DRIVER=" + self.driver + ";"
                "SERVER=" + self.server + ";"
                "DATABASE=" + self.database + ";"
                "UID=" + self.username + ";"
                "PWD=" + self.password + ";"
            )
            self.show_info_dialog("连接成功")
            # self.show_info_dialog(f"{pyodbc.drivers()}")
        except Exception as e:
            self.show_info_dialog(f"连接失败: {e}")
            self.show_info_dialog(f"{pyodbc.drivers()}")

    def execute_sql(self, sql):
        """执行SQL查询并返回结果"""
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)

            # 获取所有结果
            results = cursor.fetchall()

            # 确保资源被正确关闭
            cursor.close()

            return results
        except Exception as e:
            self.show_info_dialog(f"SQL执行错误: {e}")
            return None

    def all_players(self):
        if not self.conn:
            self.show_info_dialog("数据库未连接，请先检查配置")
            return

        sql = """SELECT CharacterID,AccountName,CharacterName FROM [dbo].[Characters]"""
        try:
            results = self.execute_sql(sql)
            if not results:
                self.show_info_dialog("未查询到在线玩家")
                return

            print(results)
        except Exception as e:
            self.show_info_dialog(f"查询过程中发生错误: {e}")

    def online_players(self):
        if not self.conn:
            self.show_info_dialog("数据库未连接，请先检查配置")
            return

        sql = """SELECT CharacterID FROM [dbo].[CharacterStatus] WHERE LastLoginDate > LastLogoutDate;"""
        try:
            results = self.execute_sql(sql)
            if not results:
                self.show_info_dialog("未查询到在线玩家")
                return

            print(results)
        except Exception as e:
            self.show_info_dialog(f"查询过程中发生错误: {e}")

    def nest_clear_character(self, character_id):
        """
        根据角色ID删除NestClear表中的记录

        Args:
            character_id (str): 要删除记录的角色ID
        """
        if not self.conn:
            self.show_info_dialog("数据库未连接，请先检查配置")
            return False

        # 防止SQL注入，使用参数化查询
        sql = """
        DELETE FROM [dbo].[NestClear] WHERE CharacterID = ?
        """

        try:
            cursor = self.conn.cursor()
            cursor.execute(sql, character_id)

            # 提交事务
            self.conn.commit()

            # 获取受影响的行数
            row_count = cursor.rowcount
            cursor.close()

            if row_count > 0:
                self.show_info_dialog(f"成功删除{row_count}条记录")
                return True
            else:
                self.show_info_dialog(f"未找到角色ID为{character_id}的记录")
                return False

        except Exception as e:
            # 发生错误时回滚事务
            self.conn.rollback()
            self.show_info_dialog(f"删除记录失败: {e}")
            return False

    def nest_clear_character_id(self, character_id):
        """
        根据角色ID删除NestClear表中的记录

        Args:
            character_id (str): 要删除记录的角色ID
        """
        if not self.conn:
            self.show_info_dialog("数据库未连接，请先检查配置")
            return False

        # 防止SQL注入，使用参数化查询
        sql = """
        DELETE FROM [dbo].[NestClear] WHERE CharacterID = ?
        """

        try:
            cursor = self.conn.cursor()
            cursor.execute(sql, character_id)

            # 提交事务
            self.conn.commit()

            # 获取受影响的行数
            row_count = cursor.rowcount
            cursor.close()

            if row_count > 0:
                self.show_info_dialog(f"成功删除{row_count}条记录")
                return True
            else:
                self.show_info_dialog(f"未找到角色ID为{character_id}的记录")
                return False

        except Exception as e:
            # 发生错误时回滚事务
            self.conn.rollback()
            self.show_info_dialog(f"删除记录失败: {e}")
            return False

    def nest_clear_all(self):
        """
        清空NestClear表中的所有记录（谨慎操作）
        """
        if not self.conn:
            self.show_info_dialog("数据库未连接，请先检查配置")
            return False

        sql = """
        DELETE FROM [dbo].[NestClear]
        """

        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)

            # 提交事务
            self.conn.commit()
            return True

        except Exception as e:
            # 发生错误时回滚事务
            self.conn.rollback()
            self.show_info_dialog(f"清空表失败: {e}")
            return False

    def send_mail_with_items(self, character_id, item_code, item_count, coin_count):
        """
        通过存储过程给指定角色发送邮件并附带物品和金币

        Args:
            character_id (int): 接收邮件的角色ID
            item_code (int): 物品代码
            item_count (int): 物品数量
            coin_count (int): 金币数量
        """
        if not self.conn:
            self.show_info_dialog("数据库未连接，请先检查配置")
            return False

        # 验证输入参数
        if not all([isinstance(character_id, int),
                    isinstance(item_code, int),
                    isinstance(item_count, int),
                    isinstance(coin_count, int)]):
            self.show_info_dialog("参数类型错误，请检查输入")
            return False

        if item_count <= 0 or coin_count < 0:
            self.show_info_dialog("物品数量必须为正数，金币数量不能为负数")
            return False

        try:
            cursor = self.conn.cursor()

            # 调用存储过程
            cursor.execute(
                "EXEC dbo.P_SendGoldAndItems_Aluo @ItemCode=?, @charid=?, @ItemCount=?, @CoinCount=?",
                item_code, character_id, item_count, coin_count
            )

            # 提交事务
            self.conn.commit()

            # 获取角色名称用于显示
            cursor.execute("SELECT CharacterName FROM Characters WHERE CharacterID = ?", character_id)
            result = cursor.fetchone()
            character_name = result[0] if result else "未知角色"

            cursor.close()

            self.show_info_dialog(
                f"已成功向角色 [{character_name}] 发送邮件，包含 {item_count} 个物品和 {coin_count} 金币")
            return True

        except Exception as e:
            # 发生错误时回滚事务
            self.conn.rollback()
            self.show_info_dialog(f"发送邮件失败: {e}")
            return False
        

if __name__ == '__main__':
    app = QApplication(sys.argv)
    window = MainWindow()
    window.show()
    sys.exit(app.exec_())